<?php
/**
 * 
 */

// Average Inquiry where they wehre 

// Lost inquiries with status to 


use Fox\Grid\FieldCat\Field;
use Fox\Grid;
use Fox\Dao\SqlFragment;
use Fox\Common\Customer;
use Fox\Html\CheckBox;
use Fox\SalesDistribution\Sales\Partner\Role;
use Doctrine\DBAL\Types\Type;
use Fox\SalesDistribution\Sales\Inquiry;
use Fox\Html\Input;
use Fox\Html\Block;
use Fox\Html\Screen;
$screen = new Screen('S','execute');
$screen->mainTitle();

$block = new Fox\Html\Block("gCriteria",Msg(Fox::MSGCL,29));
$block->add(new Input("period",true,time()));
//$block->add(new CheckBox("showym1",false),2);
$inputAgency = Xview::instance()->component("reports","agencies");
$block->add($inputAgency);

$screen->add($block);
if($screen->submitted())
{
	$inputs = $screen->get();
	if(!$screen->errors())
	{
		$output = null;
		$total = array();
		$selectDate = new DateTime($inputs['period']);
		$startDate = new DateTime($selectDate->format("Y")."-01-01");
		$endDate = $selectDate;
		// Prepare Grid
		$grid = new Grid("ReportingConcurrence");
		$grid->setXlsDownload();
		$grid->fieldcat(array(array("name1","name")));
		$grid->fieldcat()->field("name")->SetClass("separator-right ");
		
		$fragAg[] = $screen->item("gCriteria")->item("op_entity")->fragments("customerid");
		$fragments[] = $screen->item("gCriteria")->item("op_entity")->fragments("pa.customerid");
		// Load Selected Agency
		$finder = new Customer();
		$fragAg[] = new SqlFragment("type = ?",array(Customer::TYPE_OFFICE));
		$agencies = $finder->find(SqlFragment::combine($fragAg));
		if(count($agencies) > 0)
		{
			foreach($agencies as $agency)
			{
				$id = $agency->getId();
				$grid->fieldcat()->add(new Field("number","number-$id"));
				$grid->fieldcat()->field("number-$id")->SetClass("separator-dashed-right");
				$grid->fieldcat()->add(new Field("rate","rate-$id"))->SetClass("separator-right ");
				$groupingField = new Field("agency","agency-$id");
				$groupingField->setClass("gd-col-group separator-left")->label($agency->name());
				$grid->fieldcat()->buildSubTitleByGrouping(array("number-$id","rate-$id"), $groupingField);
			}
		}
	
		// Load Concurrent List
		$finder = new Customer();
		$customers = $finder->find(new SqlFragment("type = ?",array(Customer::TYPE_COMPETITOR))
									,array("name1" => false));
		foreach($customers as $customer)
		{
			$data[$customer->getId()]["name"] = $customer->name();
		}
		
		// Load Inquiry Data
		$query = \Fox\Dao::infos(Inquiry::className())->handle()->createQueryBuilder();
		$query->select('conc.customerid as competitorid','pa.customerid as agencyid','count(*) as nb')
				->from('inquiries','t')
				->innerJoin('t', 'inquiry_partners', 'conc','t.inquiryid = conc.inquiryid')
				->innerJoin('t', 'inquiry_partners', 'pa','t.inquiryid = pa.inquiryid')
				->groupBy('conc.customerid')
				;
		
		
		// Select only Lost Inquiries where successful bidder is a known
		$fragments[] = new SqlFragment("pa.role = ? ",array(Role::OPERATIONAL_ENTITY));
		$fragments[] = new SqlFragment("t.status = ? AND conc.role = ? AND t.loekz = ? AND pa.loekz = ?",
									array(Inquiry::STATUS_LOST,Role::SUCCESSFUL_BIDER,0,0));
		
		$fragments[] = new Fox\Dao\SqlFragment("t.start_date >= ? AND t.start_date <= ? ",
											array($startDate,$endDate),
											array(Type::DATE,Type::DATE));

		$fragment = Fox\Dao\SqlFragment::combine($fragments);
		$result = $fragment->pushIn($query)->execute()->fetchAll(PDO::FETCH_ASSOC);
		foreach($result as $detail)
		{
			$customerid = $detail['competitorid'];
			if(!isset($data[$customerid])) {
				try {
					continue;
					$customer = Fox\Dao::load(Customer::className(),$customerid);
					$data[$customerid]['name'] = $customer->name();
				}
				catch (Fox\Object\Exception\NotExist $e) {
					
				}
			}
			$id =$detail['agencyid']; 
			$data[$customerid]["number-$id"] = $detail['nb'];
			if(isset($total[$id]))
				$total[$id] += $detail['nb'];
			else
				$total[$id] = $detail['nb'];
		}
		foreach($data as $key => $line)
		{
			foreach($agencies as $agency)
			{
				$id = $agency->getId();
				if(isset($total[$id]) && $total[$id] > 0 && isset($line["number-$id"]))
				{
					$data[$key]["rate-$id"] = round(100* $line["number-$id"] / $total[$id],2);
				}
			}
		}
		$grid->initialize($data,true);
		$grid->setTotal();
		$output .= $grid->display();
		
		
	}
}
echo $screen->display();
if(!empty($output))
	echo $output;